﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.Services.Description;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;

namespace MockPrjTeam2.DAO
{
    public class OrganizationDao
    {

        private static OrganizationDao _organizationDao;
        public static OrganizationDao Instance
        {
            get
            {
                if (_organizationDao == null)
                    _organizationDao = new OrganizationDao();

                return _organizationDao;
            }
        }
        //method GetAllOrganizations()
        public DataTable GetAllOrganizations()
        {
            const string query = "Select OrganizationName,AddressLine1,Organizations.IsActive,Contact.FirstName +' '+ Contact.Surname as ContactName, Address.PostCode,OrgID from Organizations inner join [Address] on Organizations.AddressID=Address.AddressID inner join Contact on  Organizations.ContactID=Contact.ContactID where Organizations.IsActive=1 order by Organizations.OrganizationName asc";
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query);
            }
        }
        //method GetOrganizationsInActive()
        public DataTable GetOrganizationsInActive()
        {
          //  var of = FilterOrganizationByLetters(organizationName, flag);
            string query = "Select OrganizationName,AddressLine1,Organizations.IsActive,Contact.FirstName +' '+ Contact.Surname as ContactName, Address.PostCode,OrgID from Organizations inner join [Address] on Organizations.AddressID=Address.AddressID inner join Contact on  Organizations.ContactID=Contact.ContactID  order by  Organizations.OrganizationName asc";
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query);
            }
        }
     
        //method GetOrganizationById()
         public DataTable GetOrganizationById(int idOrganization)
        {
            string query =
                "select OrgID,OrganizationName,ShortDescription,FullDescription,PhoneNumber,Fax,Organizations.Email,WebAddress,CharityNumber,CompanyNumber,Contact.FirstName +' '+ Contact.Surname as ContactName,AddressLine1,AddressLine2,AddressLine3,PostCode,CityTown,County,SICCode,PhoneNumber,BussinessTypeName,CountryName,Country.CountryID,[Address].AddressID, BussinessType.BussinessTypeID,Contact.ContactID,OrganizationSpecialism,ServiceDisabilities,ServiceBarriers,ServiceBenefits,ServiceCircumstances,ServiceEthnicity,Accreditation,PreferredOrganization from Organizations inner join BussinessType on Organizations.BussinessTypeID= BussinessType.BussinessTypeID    inner join [Address] on Organizations.AddressID=[Address].AddressID inner join Contact on Organizations.ContactID=Contact.ContactID inner join Country on Organizations.CountryID=Country.CountryID where OrgID=@Id";
            
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@Id", SqlDbType.Int);
            sqlParameters[0].Value = idOrganization;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
           
        }
        //method AddOrganizations()
        public int AddOrganizations(Organization organization)
        {
            int organizationId;
            bool reply = false;
            bool reply1 = false;
            string query = @"insert into Organizations(OrganizationName,ShortDescription,AddressLine1,AddressLine2,AddressLine3,AddressID,CityTown,County,CountryID,BussinessTypeID,FullDescription,PhoneNumber,Fax,Email,WebAddress,CharityNumber,CompanyNumber,OrganizationSpecialism,ServiceDisabilities,ServiceBarriers,ServiceBenefits,ServiceCircumstances,ServiceEthnicity,Accreditation,PreferredOrganization,ContactID) values
                         (@OrganizationName,@ShortDescription,@AddressLine1,@AddressLine2,@AddressLine3,@AddressID,@CityTown,@County,@CountryID,@BussinessTypeID,@FullDescription,@PhoneNumber,@Fax,@Email,@WebAddress,@CharityNumber,@CompanyNumber,@OrganizationSpecialism,@ServiceDisabilities,@ServiceBarriers,@ServiceBenefits,@ServiceCircumstances,@ServiceEthnicity,@Accreditation,@PreferredOrganization,@ContactID) ";
            //string query1 = @"insert into OrganizationProgramme(OrgID,ProgrammeID) values(@OrgID,@ProgrammeID)";
            string query2 = "	select MAX(OrgID) from Organizations";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[26];

                sqlParameters[0] = new SqlParameter("@OrganizationName", SqlDbType.NVarChar);
                sqlParameters[0].Value = organization.OrganizationName;
                sqlParameters[1] = new SqlParameter("@ShortDescription", SqlDbType.NVarChar);
                sqlParameters[1].Value = organization.ShortDescription;
               
                sqlParameters[2] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar);
                sqlParameters[2].Value = organization.AddressLine1;
                sqlParameters[3] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar);
                sqlParameters[3].Value = organization.AddressLine2;
                sqlParameters[4] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar);
                sqlParameters[4].Value = organization.AddressLine3;
                sqlParameters[5] = new SqlParameter("@AddressID", SqlDbType.Int);
                sqlParameters[5].Value = organization.AddressId;
                sqlParameters[6] = new SqlParameter("@CityTown", SqlDbType.NVarChar);
                sqlParameters[6].Value = organization.CityTown;
                sqlParameters[7] = new SqlParameter("@County", SqlDbType.NVarChar);
                sqlParameters[7].Value = organization.County;
                sqlParameters[8] = new SqlParameter("@CountryID", SqlDbType.Int);
                sqlParameters[8].Value = organization.CountryId;
                sqlParameters[9] = new SqlParameter("@BussinessTypeID", SqlDbType.Int);
                sqlParameters[9].Value = organization.BusinessTypeId;
                sqlParameters[10] = new SqlParameter("@FullDescription", SqlDbType.NVarChar);
                sqlParameters[10].Value = organization.FullDescription;
                sqlParameters[11] = new SqlParameter("@PhoneNumber", SqlDbType.VarChar);
                sqlParameters[11].Value = organization.PhoneNumber;
                sqlParameters[12] = new SqlParameter("@Fax", SqlDbType.NVarChar);
                sqlParameters[12].Value = organization.Fax;
                sqlParameters[13] = new SqlParameter("@Email", SqlDbType.NVarChar);
                sqlParameters[13].Value = organization.Email;
                sqlParameters[14] = new SqlParameter("@WebAddress", SqlDbType.NVarChar);
                sqlParameters[14].Value = organization.WebAddress;
                sqlParameters[15] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar);
                sqlParameters[15].Value = organization.CharityNumber;
                sqlParameters[16] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar);
                sqlParameters[16].Value = organization.CompanyNumber;
                sqlParameters[17] = new SqlParameter("@OrganizationSpecialism",SqlDbType.NVarChar);
                sqlParameters[17].Value = organization.OrganizationSpecialism;
                sqlParameters[18] = new SqlParameter("@ServiceDisabilities", SqlDbType.NVarChar);
                sqlParameters[18].Value = organization.ServiceDisabilities;
                sqlParameters[19] = new SqlParameter("@ServiceBarriers", SqlDbType.NVarChar);
                sqlParameters[19].Value = organization.ServiceBarriers;
                sqlParameters[20] = new SqlParameter("@ServiceBenefits", SqlDbType.NVarChar);
                sqlParameters[20].Value = organization.ServiceBenefits;
                sqlParameters[21] = new SqlParameter("@ServiceCircumstances", SqlDbType.NVarChar);
                sqlParameters[21].Value = organization.ServiceCircumstances;
                sqlParameters[22] = new SqlParameter("@ServiceEthnicity", SqlDbType.NVarChar);
                sqlParameters[22].Value = organization.ServiceEthnicity;
                sqlParameters[23] = new SqlParameter("@Accreditation", SqlDbType.NVarChar);
                sqlParameters[23].Value = organization.Accreditation;
               
                sqlParameters[24] = new SqlParameter("@PreferredOrganization", SqlDbType.Bit);
                sqlParameters[24].Value = organization.PreferredOrganization;
                sqlParameters[25]=new SqlParameter("@ContactID",SqlDbType.Int);
                sqlParameters[25].Value = organization.ContactId;
                reply = helper.ExecuteInsertQuery(query, sqlParameters);
                organizationId = helper.ExecuteScalar(query2);

       
            }
            return organizationId;
        }
        //method AddOrganizationProgram
        public bool AddOrganizationProgram(int organizationId, List<int> programIds)
        {
            string query1 = @"insert into OrganizationProgramme(OrgID,ProgrammeID) values(@OrgID,@ProgrammeID)";
            
            using (var helper = new SqlHelper())
            {
                foreach (var programId in programIds)
                {
                    SqlParameter[] sqlParameters1 = new SqlParameter[2];
                    sqlParameters1[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                    sqlParameters1[0].Value = organizationId;
                    sqlParameters1[1] = new SqlParameter("@ProgrammeID", SqlDbType.Int);
                    sqlParameters1[1].Value = programId;

                    helper.ExecuteInsertQuery(query1, sqlParameters1);
                }
            }
            return true;
        }
        //method AddOrganizationService
        public bool AddOrganizationService(int organizationId, List<int> serviceIds)
        {
            string query1 = @"insert into OrganizationsService(OrgID,ServiceID) values(@OrgID,@ServiceID)";

            using (var helper = new SqlHelper())
            {
                foreach (var programId in serviceIds)
                {
                    SqlParameter[] sqlParameters1 = new SqlParameter[2];
                    sqlParameters1[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                    sqlParameters1[0].Value = organizationId;
                    sqlParameters1[1] = new SqlParameter("@ServiceID", SqlDbType.Int);
                    sqlParameters1[1].Value = programId;

                    helper.ExecuteInsertQuery(query1, sqlParameters1);
                }
            }
            return true;
        }
        //method UpdateOrganizations
        public bool UpdateOrganizations(Organization organization)
        {
            bool reply = false;
            string query = @"update Organizations set OrganizationName=@OrganizationName,ShortDescription=@ShortDescription,ContactID=@ContactID,AddressLine1=@AddressLine1,AddressLine2=@AddressLine2,AddressLine3=@AddressLine3,AddressID=@AddressID,CityTown=@CityTown,County=@County,CountryID=@CountryID,
                            BussinessTypeID=@BussinessTypeID,FullDescription=@FullDescription,PhoneNumber=@PhoneNumber,Fax=@Fax,Email=@Email,WebAddress=@WebAddress,CharityNumber=@CharityNumber,CompanyNumber=@CompanyNumber,OrganizationSpecialism=@OrganizationSpecialism,ServiceDisabilities=@ServiceDisabilities,
                            ServiceBarriers=@ServiceBarriers,ServiceBenefits=@ServiceBenefits,ServiceCircumstances=@ServiceCircumstances,ServiceEthnicity=@ServiceEthnicity,Accreditation=@Accreditation,PreferredOrganization=@PreferredOrganization where Organizations.OrgID=@OrgID ";
                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[27];

                sqlParameters[0] = new SqlParameter("@OrganizationName", SqlDbType.NVarChar);
                sqlParameters[0].Value = organization.OrganizationName;
                sqlParameters[1] = new SqlParameter("@ShortDescription", SqlDbType.NVarChar);
                sqlParameters[1].Value = organization.ShortDescription;
                sqlParameters[2] = new SqlParameter("@ContactID", SqlDbType.Int);
                sqlParameters[2].Value = organization.ContactId;
                sqlParameters[3] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar);
                sqlParameters[3].Value = organization.AddressLine1;
                sqlParameters[4] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar);
                sqlParameters[4].Value = organization.AddressLine2;
                sqlParameters[5] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar);
                sqlParameters[5].Value = organization.AddressLine3;
                sqlParameters[6] = new SqlParameter("@AddressID", SqlDbType.Int);
                sqlParameters[6].Value = organization.AddressId;
                sqlParameters[7] = new SqlParameter("@CityTown", SqlDbType.NVarChar);
                sqlParameters[7].Value = organization.CityTown;
                sqlParameters[8] = new SqlParameter("@County", SqlDbType.NVarChar);
                sqlParameters[8].Value = organization.County;
                sqlParameters[9] = new SqlParameter("@CountryID", SqlDbType.Int);
                sqlParameters[9].Value = organization.CountryId;
                sqlParameters[10] = new SqlParameter("@BussinessTypeID", SqlDbType.Int);
                sqlParameters[10].Value = organization.BusinessTypeId;
                sqlParameters[11] = new SqlParameter("@FullDescription", SqlDbType.NVarChar);
                sqlParameters[11].Value = organization.FullDescription;
                sqlParameters[12] = new SqlParameter("@PhoneNumber", SqlDbType.VarChar);
                sqlParameters[12].Value = organization.PhoneNumber;
                sqlParameters[13] = new SqlParameter("@Fax", SqlDbType.NVarChar);
                sqlParameters[13].Value = organization.Fax;
                sqlParameters[14] = new SqlParameter("@Email", SqlDbType.NVarChar);
                sqlParameters[14].Value = organization.Email;
                sqlParameters[15] = new SqlParameter("@WebAddress", SqlDbType.NVarChar);
                sqlParameters[15].Value = organization.WebAddress;
                sqlParameters[16] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar);
                sqlParameters[16].Value = organization.CharityNumber;
                sqlParameters[17] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar);
                sqlParameters[17].Value = organization.CompanyNumber;
                sqlParameters[18]=new SqlParameter("@OrgID",SqlDbType.Int);
                sqlParameters[18].Value = organization.OrganizationId;
                sqlParameters[19] = new SqlParameter("@OrganizationSpecialism", SqlDbType.NVarChar);
                sqlParameters[19].Value = organization.OrganizationSpecialism;
                sqlParameters[20] = new SqlParameter("@ServiceDisabilities", SqlDbType.NVarChar);
                sqlParameters[20].Value = organization.ServiceDisabilities;
                sqlParameters[21] = new SqlParameter("@ServiceBarriers", SqlDbType.NVarChar);
                sqlParameters[21].Value = organization.ServiceBarriers;
                sqlParameters[22] = new SqlParameter("@ServiceBenefits", SqlDbType.NVarChar);
                sqlParameters[22].Value = organization.ServiceBenefits;
                sqlParameters[23] = new SqlParameter("@ServiceCircumstances", SqlDbType.NVarChar);
                sqlParameters[23].Value = organization.ServiceCircumstances;
                sqlParameters[24] = new SqlParameter("@ServiceEthnicity", SqlDbType.NVarChar);
                sqlParameters[24].Value = organization.ServiceEthnicity;
                sqlParameters[25] = new SqlParameter("@Accreditation", SqlDbType.NVarChar);
                sqlParameters[25].Value = organization.Accreditation;

                sqlParameters[26] = new SqlParameter("@PreferredOrganization", SqlDbType.Bit);
                sqlParameters[26].Value = organization.PreferredOrganization;
            

                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
        //method SetActiveOrganization
        public bool SetActiveOrganization(int organizationId, bool status)
        {
            bool reply;
            string query = @"update Organizations set IsActive=@IsActive where OrgID=@OrgID ";
            using (var helper=new SqlHelper())
            {
                SqlParameter[]sqlParameters=new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                sqlParameters[0].Value = organizationId;
                sqlParameters[1]=new SqlParameter("@IsActive",SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
        public bool SetOrganizationInActive(int organizationId, bool status)
        {
            bool reply;
            string query = @"update Organizations set IsActive=@IsActive where OrgID=@OrgID ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                sqlParameters[0].Value = organizationId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }

        //Method FilterOrganizationByLetters
        public DataTable FilterOrganizationByLetters(string organizationName, int flag)
        {
            string query =
                "Select OrganizationName,AddressLine1,Organizations.IsActive,Contact.FirstName +' '+ Contact.Surname as ContactName, Address.PostCode,OrgID from Organizations inner join [Address] on Organizations.AddressID=Address.AddressID inner join Contact on  Organizations.ContactID=Contact.ContactID ";
            if (flag==1)
            {
                query = query + "WHERE  (Organizations.OrganizationName LIKE '['+@OrganizationName+']%') order by Organizations.OrganizationName asc";
            }
            else
            {
                query = query + "WHERE  (Organizations.OrganizationName LIKE '['+@OrganizationName+']%') And Organizations.IsActive = 1 order by Organizations.OrganizationName asc";
            }
                SqlParameter[]sqlParameters=new SqlParameter[1];
                sqlParameters[0]=new SqlParameter("@OrganizationName",SqlDbType.NVarChar);
                sqlParameters[0].Value = organizationName;
                using (var helper = new SqlHelper())
                {
                    return helper.ExecuteSelectQuery(query, sqlParameters);
                }
        }
        //method CheckOrganizationNameExist()
       public int CheckOrganizationNameExist(string orgName)
       {
           string query = "select count(OrganizationName) from Organizations where OrganizationName=@orgName";
           using (var helper=new SqlHelper())
           {
               SqlParameter[]sqlParameters=new SqlParameter[1];
               sqlParameters[0] = new SqlParameter("@orgName",SqlDbType.NVarChar);
               sqlParameters[0].Value = orgName;
               return helper.ExecuteScalar(query,sqlParameters);
           }
       }
       public bool UpdateOrganizationProgram(int organizationId, List<int> programIds)
       {
           string query1 = @"update OrganizationProgramme set ProgrammeID=@ProgrammeID where OrgID=@OrgID";
           
           using (var helper = new SqlHelper())
           {
               foreach (var programId in programIds)
               {
                   SqlParameter[] sqlParameters1 = new SqlParameter[2];
                   sqlParameters1[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                   sqlParameters1[0].Value = organizationId;
                   sqlParameters1[1] = new SqlParameter("@ProgrammeID", SqlDbType.Int);
                   sqlParameters1[1].Value = programId;

                   helper.ExecuteUpdateQuery(query1, sqlParameters1);
               }
           }
           return true;
       }
       //method AddOrganizationService
       public bool UpdateOrganizationService(int organizationId, List<int> serviceIds)
       {
           string query1 = @"update OrganizationsService set ServiceID=@ServiceID where  OrgID=@OrgID";

           using (var helper = new SqlHelper())
           {
               foreach (var programId in serviceIds)
               {
                   SqlParameter[] sqlParameters1 = new SqlParameter[2];
                   sqlParameters1[0] = new SqlParameter("@OrgID", SqlDbType.Int);
                   sqlParameters1[0].Value = organizationId;
                   sqlParameters1[1] = new SqlParameter("@ServiceID", SqlDbType.Int);
                   sqlParameters1[1].Value = programId;

                   helper.ExecuteUpdateQuery(query1, sqlParameters1);
               }
           }
           return true;
       }
       //method GetOrganizationServiceByOrgId()
      public  DataTable GetOrganizationServiceByOrgId(int orgId)
      {
          string query =
              "select OrganizationsService.ServiceID from OrganizationsService inner join Organizations on OrganizationsService.OrgID=Organizations.OrgID where OrganizationsService.OrgID=@orgId";
          SqlParameter[] sqlParameters = new SqlParameter[1];
          sqlParameters[0] = new SqlParameter("@orgId", SqlDbType.Int);
          sqlParameters[0].Value = orgId;
          using (var helper = new SqlHelper())
          {
              return helper.ExecuteSelectQuery(query, sqlParameters);
          }
          
      }
        //method GetOrganizationServiceByOrgId()
        public DataTable GetOrganizationProgrammeByOrgId(int orgId)
        {
            string query = "select OrganizationProgramme.ProgrammeID from OrganizationProgramme inner join Organizations on OrganizationProgramme.OrgID=Organizations.OrgID where OrganizationProgramme.OrgID=@orgId";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@orgId", SqlDbType.Int);
            sqlParameters[0].Value = orgId;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
    }
}